package me.zhengjie.minbearwcs.dao.outbound.inf;

import me.zhengjie.minbearwcs.entity.PO.wcs.outbound.record.OutboundRestAreaSearchPo;
import me.zhengjie.minbearwcs.entity.PO.wcs.outbound.record.StoragePalletSearchPo;
import me.zhengjie.minbearwcs.entity.PO.wcs.outbound.stockUp.*;
import me.zhengjie.minbearwcs.entity.PO.wcs.outbound.OutboundOrderDetail;
import me.zhengjie.minbearwcs.entity.PO.wcs.storage.type.StorageType;
import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Options;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.mapping.ResultSetType;

import java.security.SecureRandom;
import java.util.List;

@Mapper
public interface OutboundDao {

    @Select("SELECT * FROM outbound_order_detail where outbound_order_code = #{outboundOrderCode} and require_num > stock_up_num")
    List<OutboundOrderDetail> selectNeedStockDetail(@Param("outboundOrderCode") String outboundOrderCode);


    @Select("SELECT a.small_storage_code,d.pallet_code,d.material_code,d.material_num FROM small_storage a\n" +
            "left join medium_storage b\n" +
            "on a.medium_storage_code = b.medium_storage_code\n" +
            "left join large_storage c\n" +
            "on b.large_storage_code = c.large_storage_code\n" +
            "left join pallet d\n" +
            "on a.pallet_code = d.pallet_code\n" +
            "where d.material_code = #{materialCode} and d.material_num <= #{requireNum} and\n" +
            "c.large_storage_type = #{storageType}  and\n" +
            "a.pallet_code is not null and a.outbound_freeze_pallet is null " +
            "order by d.inbound_date")
    List<PalletSearchPo> selectCanOutboundPallet(@Param("materialCode") String materialCode,
                                                 @Param("requireNum") Integer requireNum,
                                                 @Param("storageType") StorageType storageType);


    @Select("SELECT distinct b.medium_storage_code,c.floor_num,d.inbound_date  FROM small_storage a\n" +
            "left join medium_storage b\n" +
            "on a.medium_storage_code = b.medium_storage_code\n" +
            "left join large_storage c\n" +
            "on b.large_storage_code = c.large_storage_code\n" +
            "left join pallet d\n" +
            "on a.pallet_code = d.pallet_code\n" +
            "where d.material_code = #{materialCode} and d.material_num <= #{requireNum} and\n" +
            "(c.large_storage_type = #{storageType1} or c.large_storage_type = #{storageType2}) and\n" +
            "a.pallet_code is not null and a.outbound_freeze_pallet is null\n" +
            "order by d.inbound_date")
    List<MediumStorageAndFloorNum> selectCanOutboundMediumSotrage(
            @Param("materialCode") String materialCode,
            @Param("requireNum") Integer requireNum,
            @Param("storageType1") StorageType storageType1,
            @Param("storageType2") StorageType storageType2
    );


    @Select("SELECT a.small_storage_code,a.is_entrance,a.column_num,a.outbound_freeze_pallet,\n" +
            "b.pallet_code,b.material_code,b.material_num,\n" +
            "b.inbound_order_code,b.batch_code,b.inbound_date\n" +
            "FROM small_storage a\n" +
            "left join pallet b\n" +
            "on a.pallet_code = b.pallet_code\n" +
            "where a.medium_storage_code = #{mediumStorage}\n" +
            "order by a.column_num")
    List<PalletMediumStorageSearchPo> selectPalletMediumStorage(@Param("mediumStorage") String mediumStorage);


    @Select("select a.medium_storage_code from medium_storage a\n" +
            "left join large_storage c\n" +
            "on a.large_storage_code = c.large_storage_code\n" +
            "left join\n" +
            "(\n" +
            "select medium_storage_code, count(*) as pallet_num  from small_storage\n" +
            "where\n" +
            "pallet_code is  null and inbound_freeze_pallet is  null\n" +
            "and outbound_freeze_pallet is  null  GROUP BY medium_storage_code \n" +
            ") b\n" +
            "on a.medium_storage_code = b.medium_storage_code\n" +
            "where\n" +
            "b.pallet_num is not null and c.large_storage_type = #{storageType}  \n" +
            "and a.outbound_order = #{outboundOrderCode} and b.pallet_num > 0\n" +
            "order by c.order_num,a.order_num limit 1")
    String selectMediumStorageHasNullByOutboundOrder(
            @Param("storageType") StorageType storageType,
            @Param("outboundOrderCode") String outboundOrderCode
    );


    @Select("select a.medium_storage_code from small_storage a\n" +
            "left join medium_storage b\n" +
            "on a.medium_storage_code = b.medium_storage_code\n" +
            "left join large_storage c\n" +
            "on b.large_storage_code = c.large_storage_code\n" +
            "where\n" +
            "c.large_storage_type = #{storageType} and b.outbound_order is null\n" +
            "and a.pallet_code is null and a.inbound_freeze_pallet is null\n" +
            "and a.outbound_freeze_pallet is null\n" +
            "order by c.order_num,b.order_num \n" +
            "limit 1")
    String selectNullMediumStorage(@Param("storageType") StorageType storageType);


    @Select({"<script>",
            "SELECT material_code, sum(require_num - stock_up_num) as need_num ",
            "FROM outbound_order_detail ",
            "WHERE require_num - stock_up_num > 0 ",
            "<foreach item='item' index='index' collection='outboundOrderListStr' open='AND outbound_order_code IN (' separator=',' close=')'>",
            "#{item}",
            "</foreach>",
            "GROUP BY material_code",
            "</script>"})
    @Options(useGeneratedKeys = false, keyProperty = "", resultSetType = ResultSetType.DEFAULT)
    List<StatMaterialAndNum> selectRestMaterialNum(@Param("outboundOrderListStr") List<String> outboundOrderListStr);


    @Select("select d.material_code , ifnull(count(*),0) as material_num \n" +
            "from small_storage a\n" +
            "left join medium_storage b\n" +
            "on a.medium_storage_code = b.medium_storage_code\n" +
            "left join large_storage c\n" +
            "on b.large_storage_code = c.large_storage_code\n" +
            "left join pallet d\n" +
            "on a.pallet_code = d.pallet_code\n" +
            "where c.large_storage_type = #{storageType}  \n" +
            "and a.outbound_freeze_pallet is null\n" +
            "group by d.material_code\n" +
            "having d.material_code = #{materialCode}")
    MaterialAndNum selectMaterialByStorageType(
            @Param("storageType") StorageType storageType,
            @Param("materialCode") String materialCode
    );


    @Select("SELECT distinct b.medium_storage_code ,c.floor_num,d.inbound_date  FROM small_storage a\n" +
            "left join medium_storage b\n" +
            "on a.medium_storage_code = b.medium_storage_code\n" +
            "left join large_storage c\n" +
            "on b.large_storage_code = c.large_storage_code\n" +
            "left join pallet d\n" +
            "on a.pallet_code = d.pallet_code\n" +
            "where d.material_code = #{materialCode} and \n" +
            "(c.large_storage_type = #{storageType1} or c.large_storage_type = #{storageType2}) and\n" +
            "a.pallet_code is not null and a.outbound_freeze_pallet is null\n" +
            "order by d.inbound_date")
    List<MediumStorageAndFloorNum> selectCanOutboundMediumSotrageNoNeedNum(
            @Param("materialCode") String materialCode,
            @Param("storageType1") StorageType storageType1,
            @Param("storageType2") StorageType storageType2
    );


    @Select({"<script>",
            "SELECT * FROM outbound_order_detail ",
            "WHERE outbound_order_code IN ",
            "<foreach item='item' index='index' collection='outboundOrderList' open='(' separator=',' close=')'>",
            "    #{item}",
            "</foreach>",
            "AND require_num > stock_up_num ",
            "AND material_code = #{materialCode}",
            "</script>"})
    @Options(useGeneratedKeys = false, keyProperty = "", resultSetType = ResultSetType.DEFAULT)
    List<OutboundOrderDetail> selectNeedStockUpOutboundDetailBymaterial(
            @Param("outboundOrderList") List<String> outboundOrderList, // 注意这里需要是List类型
            @Param("materialCode") String materialCode
    );


    @Select("SELECT a.small_storage_code,a.is_entrance,a.column_num,a.outbound_freeze_pallet,a.inbound_freeze_pallet,\n" +
            "b.pallet_code,b.material_code,b.material_num,\n" +
            "b.inbound_order_code,b.batch_code,b.inbound_date , d.floor_num\n" +
            "FROM small_storage a\n" +
            "left join pallet b\n" +
            "on a.pallet_code = b.pallet_code or a.inbound_freeze_pallet = b.pallet_code\n" +
            "left join medium_storage c\n" +
            "on a.medium_storage_code = c.medium_storage_code\n" +
            "left join large_storage d\n" +
            "on c.large_storage_code = d.large_storage_code\n" +
            "where a.medium_storage_code = #{mediumStorage}\n" +
            "order by a.column_num")
    List<StoragePalletSearchPo> selectStoragePalletByMediumStorage(@Param("mediumStorage") String mediumStorage);


    @Select("SELECT a.small_storage_code,d.pallet_code,d.material_code,d.material_num FROM small_storage a\n" +
            "left join medium_storage b\n" +
            "on a.medium_storage_code = b.medium_storage_code\n" +
            "left join large_storage c\n" +
            "on b.large_storage_code = c.large_storage_code\n" +
            "left join pallet d\n" +
            "on a.pallet_code = d.pallet_code\n" +
            "where d.material_code = #{materialCode} and d.material_num >= #{requireNum} and\n" +
            "c.large_storage_type = #{storageType}  and\n" +
            "a.pallet_code is not null and a.outbound_freeze_pallet is null " +
            "order by d.inbound_date limit 1")
    PalletSearchPo selectCanSortPallet(@Param("materialCode") String materialCode,
                                       @Param("requireNum") Integer requireNum,
                                       @Param("storageType") StorageType storageType);


    @Select("SELECT count(*) FROM sort_pallet_detail a\n" +
            "left join outbound_order b\n" +
            "on a.outbound_order_code = b.outbound_order_code\n" +
            "where a.pallet_code = #{palletCode} and  b.outbound_order_code is not null")
    Integer selectFakerPalletHasProcessOrderNum(@Param("palletCode") String palletCode);


    @Select("SELECT  a.small_storage_code,a.pallet_code,d.material_code,d.material_num,c.floor_num,d.inbound_date  \n" +
            "FROM small_storage a\n" +
            "left join medium_storage b\n" +
            "on a.medium_storage_code = b.medium_storage_code\n" +
            "left join large_storage c\n" +
            "on b.large_storage_code = c.large_storage_code\n" +
            "left join pallet d\n" +
            "on a.pallet_code = d.pallet_code\n" +
            "where d.material_code = #{materialCode} and d.material_num <= #{requireNum} and\n" +
            "c.large_storage_type = #{storageType} and\n" +
            "a.pallet_code is not null and a.outbound_freeze_pallet is null\n" +
            "order by d.inbound_date\n" +
            "limit 1")
    OutboundRestAreaSearchPo selectCanUseRestAreaPallet(
            @Param("materialCode") String materialCode,
            @Param("requireNum") Integer requireNum,
            @Param("storageType") StorageType storageType
    );

}
